
CREATE OR REPLACE FUNCTION func_createTable() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(pc.*) INTO VCOUNT FROM pg_class pc, pg_namespace pn WHERE pc.relname = 'assoc_request_type_property' AND pc.relnamespace = pn.oid AND pn.nspname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE TABLE <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY (
	COD_BACK_OFFICE_REQUEST_TYPE NUMERIC(20, 0) NOT NULL,
	COD_BACK_OFFICE_REQ_PROP NUMERIC(20, 0) NOT NULL
);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createTable();
 DROP FUNCTION func_createTable();


CREATE OR REPLACE FUNCTION func_createTable() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(pc.*) INTO VCOUNT FROM pg_class pc, pg_namespace pn WHERE pc.relname = 'back_office_request' AND pc.relnamespace = pn.oid AND pn.nspname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE TABLE <SCHEMA>.BACK_OFFICE_REQUEST (
	COD_BACK_OFFICE_REQUEST NUMERIC(20, 0) NOT NULL,
	COD_BACK_OFFICE_REQUEST_TYPE NUMERIC(20, 0) NOT NULL,
	COD_BACK_OFFICE_STATUS NUMERIC(20, 0) NOT NULL,
	COMMAND_FORM OID NOT NULL,
	TS_REQ TIMESTAMP NOT NULL,
	USU_REQ VARCHAR(255) NOT NULL,
	VERSION NUMERIC(20, 0) NOT NULL
);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createTable();
 DROP FUNCTION func_createTable();


CREATE OR REPLACE FUNCTION func_createTable() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(pc.*) INTO VCOUNT FROM pg_class pc, pg_namespace pn WHERE pc.relname = 'back_office_request_property' AND pc.relnamespace = pn.oid AND pn.nspname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE TABLE <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY (
	COD_BACK_OFFICE_REQ_PROP NUMERIC(20, 0) NOT NULL,
	DESCR VARCHAR(255) NOT NULL,
	MNE VARCHAR(255) NOT NULL,
	NUM_DEC NUMERIC(2, 0),
	TP NUMERIC(10, 0) NOT NULL
);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createTable();
 DROP FUNCTION func_createTable();


CREATE OR REPLACE FUNCTION func_createTable() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(pc.*) INTO VCOUNT FROM pg_class pc, pg_namespace pn WHERE pc.relname = 'back_office_request_revision' AND pc.relnamespace = pn.oid AND pn.nspname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE TABLE <SCHEMA>.BACK_OFFICE_REQUEST_REVISION (
	COD_BACK_OFFICE_REQUEST NUMERIC(20, 0) NOT NULL,
	COD_REQUEST_REVISION NUMERIC(20, 0) NOT NULL,
	REVISION_COMMENT VARCHAR(512),
	REVISION_OUTCOME NUMERIC(1, 0) NOT NULL,
	TS TIMESTAMP NOT NULL,
	USU_REV VARCHAR(255) NOT NULL
);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createTable();
 DROP FUNCTION func_createTable();


CREATE OR REPLACE FUNCTION func_createTable() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(pc.*) INTO VCOUNT FROM pg_class pc, pg_namespace pn WHERE pc.relname = 'back_office_request_status' AND pc.relnamespace = pn.oid AND pn.nspname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE TABLE <SCHEMA>.BACK_OFFICE_REQUEST_STATUS (
	COD_BACK_OFFICE_STATUS NUMERIC(20, 0) NOT NULL,
	NM_STATUS VARCHAR(255) NOT NULL,
	VERSION NUMERIC(20, 0) NOT NULL
);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createTable();
 DROP FUNCTION func_createTable();


CREATE OR REPLACE FUNCTION func_createTable() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(pc.*) INTO VCOUNT FROM pg_class pc, pg_namespace pn WHERE pc.relname = 'back_office_request_type' AND pc.relnamespace = pn.oid AND pn.nspname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE TABLE <SCHEMA>.BACK_OFFICE_REQUEST_TYPE (
	COD_BACK_OFFICE_REQUEST_TYPE NUMERIC(20, 0) NOT NULL,
	FORM_HANDLER VARCHAR(255) NOT NULL,
	MNE VARCHAR(255) NOT NULL
);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createTable();
 DROP FUNCTION func_createTable();


CREATE OR REPLACE FUNCTION func_createTable() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(pc.*) INTO VCOUNT FROM pg_class pc, pg_namespace pn WHERE pc.relname = 'back_office_request_value' AND pc.relnamespace = pn.oid AND pn.nspname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE TABLE <SCHEMA>.BACK_OFFICE_REQUEST_VALUE (
	COD_BACK_OFFICE_REQUEST NUMERIC(20, 0) NOT NULL,
	COD_BACK_OFFICE_REQ_PROP NUMERIC(20, 0) NOT NULL,
	COD_BACK_OFFICE_REQUEST_VALUE NUMERIC(20, 0) NOT NULL,
	TP NUMERIC(10, 0) NOT NULL,
	VL_BIG_DECIMAL NUMERIC(29, 16),
	VL_DATA DATE,
	VL_STRING VARCHAR(255)
);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createTable();
 DROP FUNCTION func_createTable();


\echo 'Incluindo PK PK_ASRQTP na Tabela ASSOC_REQUEST_TYPE_PROPERTY'

CREATE OR REPLACE FUNCTION func_dropToCreatePrimeryKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.assoc_request_type_property'::REGCLASS AND conname= 'pk_asrqtp';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY DROP CONSTRAINT PK_ASRQTP;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreatePrimeryKey();
 DROP FUNCTION func_dropToCreatePrimeryKey();


ALTER TABLE <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY ADD CONSTRAINT PK_ASRQTP PRIMARY KEY (COD_BACK_OFFICE_REQUEST_TYPE, COD_BACK_OFFICE_REQ_PROP);
\echo 'Incluindo/Alterando AK_ASRQTP_JT_0 na Tabela ASSOC_REQUEST_TYPE_PROPERTY'

\echo 'Excluindo AK_ASRQTP_JT_0 da Tabela ASSOC_REQUEST_TYPE_PROPERTY'

CREATE OR REPLACE FUNCTION func_dropUniqueConstraint() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.assoc_request_type_property'::REGCLASS AND conname= 'ak_asrqtp_jt_0';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY DROP CONSTRAINT AK_ASRQTP_JT_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropUniqueConstraint();
 DROP FUNCTION func_dropUniqueConstraint();


ALTER TABLE <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY ADD CONSTRAINT AK_ASRQTP_JT_0 UNIQUE (COD_BACK_OFFICE_REQUEST_TYPE, COD_BACK_OFFICE_REQ_PROP);

COMMENT ON TABLE <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY IS 'ASSOCIACAO ENTRE UM TIPO DE REQUISICAO E AS PROPRIEDADES UTILIZADAS POR SUAS REQUISICOES.';
COMMENT ON COLUMN <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY.COD_BACK_OFFICE_REQUEST_TYPE IS 'CODIGO DO TIPO DE REQUISICAO.';
COMMENT ON COLUMN <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY.COD_BACK_OFFICE_REQ_PROP IS 'CODIGO DA PROPRIEDADE DE REQUISICAO.';
\echo 'Incluindo PK PK_BCOFRQ na Tabela BACK_OFFICE_REQUEST'

CREATE OR REPLACE FUNCTION func_dropToCreatePrimeryKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request'::REGCLASS AND conname= 'pk_bcofrq';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST DROP CONSTRAINT PK_BCOFRQ;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreatePrimeryKey();
 DROP FUNCTION func_dropToCreatePrimeryKey();


ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST ADD CONSTRAINT PK_BCOFRQ PRIMARY KEY (COD_BACK_OFFICE_REQUEST);
COMMENT ON TABLE <SCHEMA>.BACK_OFFICE_REQUEST IS 'UMA REQUISICAO A SER ENVIADA AO BACK OFFICE';
COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST.COD_BACK_OFFICE_REQUEST IS 'CODIGO DA REQUISICAO AO BACK OFFICE.';
COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST.COD_BACK_OFFICE_REQUEST_TYPE IS 'CODIGO DO TIPO DE REQUISICAO.';
COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST.COD_BACK_OFFICE_STATUS IS 'CODIGO DO STATUS DA REQUISICAO AO BACK OFFICE.';
COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST.COMMAND_FORM IS 'CONTEUDO DO FORMULARIO DA REQUISICAO.';
COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST.TS_REQ IS 'DATA E HORA EM QUE A REQUISICAO FOI FEITA.';
COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST.USU_REQ IS 'USUARIO QUE CRIOU A REQUISICAO.';
COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST.VERSION IS 'VERSAO DO REGISTRO.';
\echo 'Incluindo PK PK_BORQPT na Tabela BACK_OFFICE_REQUEST_PROPERTY'

CREATE OR REPLACE FUNCTION func_dropToCreatePrimeryKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_property'::REGCLASS AND conname= 'pk_borqpt';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY DROP CONSTRAINT PK_BORQPT;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreatePrimeryKey();
 DROP FUNCTION func_dropToCreatePrimeryKey();


ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY ADD CONSTRAINT PK_BORQPT PRIMARY KEY (COD_BACK_OFFICE_REQ_PROP);

\echo 'Incluindo/Alterando AK_BORQPT_JT_0 na Tabela BACK_OFFICE_REQUEST_PROPERTY'

\echo 'Excluindo AK_BORQPT_JT_0 da Tabela BACK_OFFICE_REQUEST_PROPERTY'

CREATE OR REPLACE FUNCTION func_dropUniqueConstraint() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_property'::REGCLASS AND conname= 'ak_borqpt_jt_0';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY DROP CONSTRAINT AK_BORQPT_JT_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropUniqueConstraint();
 DROP FUNCTION func_dropUniqueConstraint();


ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY ADD CONSTRAINT AK_BORQPT_JT_0 UNIQUE (MNE);


COMMENT ON TABLE <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY IS 'PROPRIEDADE DE REQUISICOES DE BACK OFFICE.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY.COD_BACK_OFFICE_REQ_PROP IS 'CODIGO DA PROPRIEDADE DE REQUISICOES DE BACK OFFICE.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY.DESCR IS 'DESCRICAO DA PROPRIEDADE.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY.MNE IS 'MNEMONICO DA PROPRIEDADE.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY.NUM_DEC IS 'NUMERO DE CASAS DECIMAIS QUE DEVE SER APRESENTADO.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY.TP IS 'TIPO DA PROPRIEDADE. (0) STRING, (1) NUMERICO, (2) DATE.';

\echo 'Incluindo PK PK_BORQRV na Tabela BACK_OFFICE_REQUEST_REVISION'

CREATE OR REPLACE FUNCTION func_dropToCreatePrimeryKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_revision'::REGCLASS AND conname= 'pk_borqrv';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_REVISION DROP CONSTRAINT PK_BORQRV;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreatePrimeryKey();
 DROP FUNCTION func_dropToCreatePrimeryKey();


ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_REVISION ADD CONSTRAINT PK_BORQRV PRIMARY KEY (COD_REQUEST_REVISION);

COMMENT ON TABLE <SCHEMA>.BACK_OFFICE_REQUEST_REVISION IS 'REVISAO DE REQUISICAO AO BACK OFFICE.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_REVISION.COD_BACK_OFFICE_REQUEST IS 'CODIGO DA REQUISICAO REVISADA.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_REVISION.COD_REQUEST_REVISION IS 'CODIGO DA REVISAO DE REQUISICAO AO BACK OFFICE';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_REVISION.REVISION_COMMENT IS 'OBSERVACOES FEITAS DURANTE A REVISAO.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_REVISION.REVISION_OUTCOME IS 'RESULTADO DE UMA REVISAO DE REQUISICAO. (0) AUTHORIZED, (1) REJECTED, (2) NEUTRAL.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_REVISION.TS IS 'HORA DA REVISAO DA REQUISICAO.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_REVISION.USU_REV IS 'NOME DO USUARIO QUE FEZ A REVISAO.';

\echo 'Incluindo PK PK_BCOFST na Tabela BACK_OFFICE_REQUEST_STATUS'

CREATE OR REPLACE FUNCTION func_dropToCreatePrimeryKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_status'::REGCLASS AND conname= 'pk_bcofst';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_STATUS DROP CONSTRAINT PK_BCOFST;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreatePrimeryKey();
 DROP FUNCTION func_dropToCreatePrimeryKey();


ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_STATUS ADD CONSTRAINT PK_BCOFST PRIMARY KEY (COD_BACK_OFFICE_STATUS);

COMMENT ON TABLE <SCHEMA>.BACK_OFFICE_REQUEST_STATUS IS 'O STATUS DE UMA REQUISICAO A SER ENVIADA AO BACK OFFICE';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_STATUS.COD_BACK_OFFICE_STATUS IS 'CODIGO DO STATUS DA REQUISICAO AO BACK OFFICE.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_STATUS.NM_STATUS IS 'NOME DO STATUS.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_STATUS.VERSION IS 'VERSAO DO REGISTRO.';

\echo 'Incluindo PK PK_BORQTP na Tabela BACK_OFFICE_REQUEST_TYPE'

CREATE OR REPLACE FUNCTION func_dropToCreatePrimeryKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_type'::REGCLASS AND conname= 'pk_borqtp';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_TYPE DROP CONSTRAINT PK_BORQTP;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreatePrimeryKey();
 DROP FUNCTION func_dropToCreatePrimeryKey();


ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_TYPE ADD CONSTRAINT PK_BORQTP PRIMARY KEY (COD_BACK_OFFICE_REQUEST_TYPE);

\echo 'Incluindo/Alterando AK_BORQTP_JT_0 na Tabela BACK_OFFICE_REQUEST_TYPE'

\echo 'Excluindo AK_BORQTP_JT_0 da Tabela BACK_OFFICE_REQUEST_TYPE'

CREATE OR REPLACE FUNCTION func_dropUniqueConstraint() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_type'::REGCLASS AND conname= 'ak_borqtp_jt_0';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_TYPE DROP CONSTRAINT AK_BORQTP_JT_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropUniqueConstraint();
 DROP FUNCTION func_dropUniqueConstraint();


ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_TYPE ADD CONSTRAINT AK_BORQTP_JT_0 UNIQUE (MNE);


COMMENT ON TABLE <SCHEMA>.BACK_OFFICE_REQUEST_TYPE IS 'ARMAZENA O TIPO DE REQUISICAO AO BACK OFFICE.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_TYPE.COD_BACK_OFFICE_REQUEST_TYPE IS 'CODIGO DO TIPO DE REQUISICAO';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_TYPE.FORM_HANDLER IS 'IDENTIFICADOR DO RESPONSAVEL POR TRATAR A REQUISICAO';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_TYPE.MNE IS 'MNEMONICO DO TIPO DE REQUISICAO';

\echo 'Incluindo PK PK_BORQVL na Tabela BACK_OFFICE_REQUEST_VALUE'

CREATE OR REPLACE FUNCTION func_dropToCreatePrimeryKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_value'::REGCLASS AND conname= 'pk_borqvl';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_VALUE DROP CONSTRAINT PK_BORQVL;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreatePrimeryKey();
 DROP FUNCTION func_dropToCreatePrimeryKey();


ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_VALUE ADD CONSTRAINT PK_BORQVL PRIMARY KEY (COD_BACK_OFFICE_REQUEST_VALUE);

\echo 'Incluindo/Alterando AK_BORQVL_JT_0 na Tabela BACK_OFFICE_REQUEST_VALUE'

\echo 'Excluindo AK_BORQVL_JT_0 da Tabela BACK_OFFICE_REQUEST_VALUE'

CREATE OR REPLACE FUNCTION func_dropUniqueConstraint() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_value'::REGCLASS AND conname= 'ak_borqvl_jt_0';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_VALUE DROP CONSTRAINT AK_BORQVL_JT_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropUniqueConstraint();
 DROP FUNCTION func_dropUniqueConstraint();


ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_VALUE ADD CONSTRAINT AK_BORQVL_JT_0 UNIQUE (COD_BACK_OFFICE_REQUEST, COD_BACK_OFFICE_REQ_PROP);


COMMENT ON TABLE <SCHEMA>.BACK_OFFICE_REQUEST_VALUE IS 'VALORES DE REQUISICOES DE BACK OFFICE.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_VALUE.COD_BACK_OFFICE_REQUEST IS 'CODIGO DA REQUISICAO QUE POSSUI O VALOR.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_VALUE.COD_BACK_OFFICE_REQ_PROP IS 'CODIGO DA PROPRIEDADE QUE ESTE VALOR REPRESENTA';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_VALUE.COD_BACK_OFFICE_REQUEST_VALUE IS 'CODIGO DO VALOR DA REQUISICAO.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_VALUE.TP IS 'TIPO DE VALOR. (0) STRING, (1) NUMERICO, (2) DATE.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_VALUE.VL_BIG_DECIMAL IS 'VALOR DA PROPRIEDADE DO TIPO NUMERICO.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_VALUE.VL_DATA IS 'VALOR DA PROPRIEDADE DO TIPO DATA.';

COMMENT ON COLUMN <SCHEMA>.BACK_OFFICE_REQUEST_VALUE.VL_STRING IS 'VALOR DA PROPRIEDADE DO TIPO STRING.';

\echo 'Incluindo/Alterando FK/CK FK_BCOFRQ_BORQRV da Tabela BACK_OFFICE_REQUEST_REVISION'

CREATE OR REPLACE FUNCTION func_dropToCreateForeignKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_revision'::REGCLASS AND conname= 'fk_bcofrq_borqrv';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_REVISION DROP CONSTRAINT FK_BCOFRQ_BORQRV;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreateForeignKey();
 DROP FUNCTION func_dropToCreateForeignKey();

ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_REVISION ADD CONSTRAINT FK_BCOFRQ_BORQRV FOREIGN KEY (COD_BACK_OFFICE_REQUEST) REFERENCES <SCHEMA>.BACK_OFFICE_REQUEST (COD_BACK_OFFICE_REQUEST);

\echo 'Incluindo/Alterando FK/CK FK_BCOFRQ_BORQVL da Tabela BACK_OFFICE_REQUEST_VALUE'

CREATE OR REPLACE FUNCTION func_dropToCreateForeignKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_value'::REGCLASS AND conname= 'fk_bcofrq_borqvl';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_VALUE DROP CONSTRAINT FK_BCOFRQ_BORQVL;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreateForeignKey();
 DROP FUNCTION func_dropToCreateForeignKey();

ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_VALUE ADD CONSTRAINT FK_BCOFRQ_BORQVL FOREIGN KEY (COD_BACK_OFFICE_REQUEST) REFERENCES <SCHEMA>.BACK_OFFICE_REQUEST (COD_BACK_OFFICE_REQUEST);

\echo 'Incluindo/Alterando FK/CK FK_BCOFST_BCOFRQ da Tabela BACK_OFFICE_REQUEST'

CREATE OR REPLACE FUNCTION func_dropToCreateForeignKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request'::REGCLASS AND conname= 'fk_bcofst_bcofrq';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST DROP CONSTRAINT FK_BCOFST_BCOFRQ;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreateForeignKey();
 DROP FUNCTION func_dropToCreateForeignKey();

ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST ADD CONSTRAINT FK_BCOFST_BCOFRQ FOREIGN KEY (COD_BACK_OFFICE_STATUS) REFERENCES <SCHEMA>.BACK_OFFICE_REQUEST_STATUS (COD_BACK_OFFICE_STATUS);

\echo 'Incluindo/Alterando FK/CK FK_BORQPT_ASRQTP da Tabela ASSOC_REQUEST_TYPE_PROPERTY'

CREATE OR REPLACE FUNCTION func_dropToCreateForeignKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.assoc_request_type_property'::REGCLASS AND conname= 'fk_borqpt_asrqtp';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY DROP CONSTRAINT FK_BORQPT_ASRQTP;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreateForeignKey();
 DROP FUNCTION func_dropToCreateForeignKey();

ALTER TABLE <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY ADD CONSTRAINT FK_BORQPT_ASRQTP FOREIGN KEY (COD_BACK_OFFICE_REQ_PROP) REFERENCES <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY (COD_BACK_OFFICE_REQ_PROP);

\echo 'Incluindo/Alterando FK/CK FK_BORQPT_BORQVL da Tabela BACK_OFFICE_REQUEST_VALUE'

CREATE OR REPLACE FUNCTION func_dropToCreateForeignKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_value'::REGCLASS AND conname= 'fk_borqpt_borqvl';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_VALUE DROP CONSTRAINT FK_BORQPT_BORQVL;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreateForeignKey();
 DROP FUNCTION func_dropToCreateForeignKey();

ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_VALUE ADD CONSTRAINT FK_BORQPT_BORQVL FOREIGN KEY (COD_BACK_OFFICE_REQ_PROP) REFERENCES <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY (COD_BACK_OFFICE_REQ_PROP);

\echo 'Incluindo/Alterando FK/CK FK_BORQTP_ASRQTP da Tabela ASSOC_REQUEST_TYPE_PROPERTY'

CREATE OR REPLACE FUNCTION func_dropToCreateForeignKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.assoc_request_type_property'::REGCLASS AND conname= 'fk_borqtp_asrqtp';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY DROP CONSTRAINT FK_BORQTP_ASRQTP;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreateForeignKey();
 DROP FUNCTION func_dropToCreateForeignKey();

ALTER TABLE <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY ADD CONSTRAINT FK_BORQTP_ASRQTP FOREIGN KEY (COD_BACK_OFFICE_REQUEST_TYPE) REFERENCES <SCHEMA>.BACK_OFFICE_REQUEST_TYPE (COD_BACK_OFFICE_REQUEST_TYPE);

\echo 'Incluindo/Alterando FK/CK FK_BORQTP_BCOFRQ da Tabela BACK_OFFICE_REQUEST'

CREATE OR REPLACE FUNCTION func_dropToCreateForeignKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request'::REGCLASS AND conname= 'fk_borqtp_bcofrq';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST DROP CONSTRAINT FK_BORQTP_BCOFRQ;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreateForeignKey();
 DROP FUNCTION func_dropToCreateForeignKey();

ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST ADD CONSTRAINT FK_BORQTP_BCOFRQ FOREIGN KEY (COD_BACK_OFFICE_REQUEST_TYPE) REFERENCES <SCHEMA>.BACK_OFFICE_REQUEST_TYPE (COD_BACK_OFFICE_REQUEST_TYPE);

\echo 'Incluindo CK_BORQPT_JT_0 na Tabela BACK_OFFICE_REQUEST_PROPERTY'

CREATE OR REPLACE FUNCTION func_dropCheckConstraint() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_property'::REGCLASS AND conname= 'ck_borqpt_jt_0';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY DROP CONSTRAINT CK_BORQPT_JT_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropCheckConstraint();
 DROP FUNCTION func_dropCheckConstraint();


ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_PROPERTY ADD CONSTRAINT CK_BORQPT_JT_0 CHECK ((TP = ANY (ARRAY[(0)::NUMERIC, (1)::NUMERIC, (2)::NUMERIC])));


\echo 'Incluindo CK_BORQRV_JT_0 na Tabela BACK_OFFICE_REQUEST_REVISION'

CREATE OR REPLACE FUNCTION func_dropCheckConstraint() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_revision'::REGCLASS AND conname= 'ck_borqrv_jt_0';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_REVISION DROP CONSTRAINT CK_BORQRV_JT_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropCheckConstraint();
 DROP FUNCTION func_dropCheckConstraint();


ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_REVISION ADD CONSTRAINT CK_BORQRV_JT_0 CHECK ((REVISION_OUTCOME = ANY (ARRAY[(0)::NUMERIC, (1)::NUMERIC, (2)::NUMERIC])));


\echo 'Incluindo CK_BORQVL_JT_0 na Tabela BACK_OFFICE_REQUEST_VALUE'

CREATE OR REPLACE FUNCTION func_dropCheckConstraint() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.back_office_request_value'::REGCLASS AND conname= 'ck_borqvl_jt_0';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_VALUE DROP CONSTRAINT CK_BORQVL_JT_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropCheckConstraint();
 DROP FUNCTION func_dropCheckConstraint();


ALTER TABLE <SCHEMA>.BACK_OFFICE_REQUEST_VALUE ADD CONSTRAINT CK_BORQVL_JT_0 CHECK ((TP = ANY (ARRAY[(0)::NUMERIC, (1)::NUMERIC, (2)::NUMERIC])));


\echo 'Criando Index IX_ASRQTP_JT_0'

CREATE OR REPLACE FUNCTION func_dropIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_asrqtp_jt_0' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 1 THEN
          DROP INDEX IX_ASRQTP_JT_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropIndexElement();
 DROP FUNCTION func_dropIndexElement();

CREATE OR REPLACE FUNCTION func_createIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_asrqtp_jt_0' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE INDEX IX_ASRQTP_JT_0 ON <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY (COD_BACK_OFFICE_REQUEST_TYPE ASC);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createIndexElement();
 DROP FUNCTION func_createIndexElement();



\echo 'Criando Index IX_ASRQTP_JT_1'

CREATE OR REPLACE FUNCTION func_dropIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_asrqtp_jt_1' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 1 THEN
          DROP INDEX IX_ASRQTP_JT_1;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropIndexElement();
 DROP FUNCTION func_dropIndexElement();

CREATE OR REPLACE FUNCTION func_createIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_asrqtp_jt_1' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE INDEX IX_ASRQTP_JT_1 ON <SCHEMA>.ASSOC_REQUEST_TYPE_PROPERTY (COD_BACK_OFFICE_REQ_PROP ASC);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createIndexElement();
 DROP FUNCTION func_createIndexElement();



\echo 'Criando Index IX_BCOFRQ_JT_0'

CREATE OR REPLACE FUNCTION func_dropIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_bcofrq_jt_0' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 1 THEN
          DROP INDEX IX_BCOFRQ_JT_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropIndexElement();
 DROP FUNCTION func_dropIndexElement();

CREATE OR REPLACE FUNCTION func_createIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_bcofrq_jt_0' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE INDEX IX_BCOFRQ_JT_0 ON <SCHEMA>.BACK_OFFICE_REQUEST (COD_BACK_OFFICE_REQUEST_TYPE ASC);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createIndexElement();
 DROP FUNCTION func_createIndexElement();



\echo 'Criando Index IX_BCOFRQ_JT_1'

CREATE OR REPLACE FUNCTION func_dropIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_bcofrq_jt_1' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 1 THEN
          DROP INDEX IX_BCOFRQ_JT_1;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropIndexElement();
 DROP FUNCTION func_dropIndexElement();

CREATE OR REPLACE FUNCTION func_createIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_bcofrq_jt_1' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE INDEX IX_BCOFRQ_JT_1 ON <SCHEMA>.BACK_OFFICE_REQUEST (COD_BACK_OFFICE_STATUS ASC);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createIndexElement();
 DROP FUNCTION func_createIndexElement();



\echo 'Criando Index IX_BORQRV_JT_0'

CREATE OR REPLACE FUNCTION func_dropIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_borqrv_jt_0' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 1 THEN
          DROP INDEX IX_BORQRV_JT_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropIndexElement();
 DROP FUNCTION func_dropIndexElement();

CREATE OR REPLACE FUNCTION func_createIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_borqrv_jt_0' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE INDEX IX_BORQRV_JT_0 ON <SCHEMA>.BACK_OFFICE_REQUEST_REVISION (COD_BACK_OFFICE_REQUEST ASC);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createIndexElement();
 DROP FUNCTION func_createIndexElement();








\echo 'Criando Index IX_BORQVL_JT_0'

CREATE OR REPLACE FUNCTION func_dropIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_borqvl_jt_0' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 1 THEN
          DROP INDEX IX_BORQVL_JT_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropIndexElement();
 DROP FUNCTION func_dropIndexElement();

CREATE OR REPLACE FUNCTION func_createIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_borqvl_jt_0' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE INDEX IX_BORQVL_JT_0 ON <SCHEMA>.BACK_OFFICE_REQUEST_VALUE (COD_BACK_OFFICE_REQUEST ASC);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createIndexElement();
 DROP FUNCTION func_createIndexElement();


\echo 'Criando Index IX_BORQVL_JT_1'

CREATE OR REPLACE FUNCTION func_dropIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_borqvl_jt_1' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 1 THEN
          DROP INDEX IX_BORQVL_JT_1;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropIndexElement();
 DROP FUNCTION func_dropIndexElement();

CREATE OR REPLACE FUNCTION func_createIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_borqvl_jt_1' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE INDEX IX_BORQVL_JT_1 ON <SCHEMA>.BACK_OFFICE_REQUEST_VALUE (COD_BACK_OFFICE_REQ_PROP ASC);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createIndexElement();
 DROP FUNCTION func_createIndexElement();


\echo 'Incluindo Sequence SEQ_BCOFRQ'

CREATE OR REPLACE FUNCTION func_createSequence() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_statio_all_sequences WHERE relname = 'seq_bcofrq' and schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE SEQUENCE <SCHEMA>.SEQ_BCOFRQ START WITH 100000;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createSequence();
 DROP FUNCTION func_createSequence();



\echo 'Incluindo Sequence SEQ_BCOFST'

CREATE OR REPLACE FUNCTION func_createSequence() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_statio_all_sequences WHERE relname = 'seq_bcofst' and schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE SEQUENCE <SCHEMA>.SEQ_BCOFST START WITH 100000;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createSequence();
 DROP FUNCTION func_createSequence();



\echo 'Incluindo Sequence SEQ_BORQPT'

CREATE OR REPLACE FUNCTION func_createSequence() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_statio_all_sequences WHERE relname = 'seq_borqpt' and schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE SEQUENCE <SCHEMA>.SEQ_BORQPT START WITH 100000;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createSequence();
 DROP FUNCTION func_createSequence();



\echo 'Incluindo Sequence SEQ_BORQRV'

CREATE OR REPLACE FUNCTION func_createSequence() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_statio_all_sequences WHERE relname = 'seq_borqrv' and schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE SEQUENCE <SCHEMA>.SEQ_BORQRV START WITH 100000;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createSequence();
 DROP FUNCTION func_createSequence();



\echo 'Incluindo Sequence SEQ_BORQTP'

CREATE OR REPLACE FUNCTION func_createSequence() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_statio_all_sequences WHERE relname = 'seq_borqtp' and schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE SEQUENCE <SCHEMA>.SEQ_BORQTP START WITH 100000;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createSequence();
 DROP FUNCTION func_createSequence();



\echo 'Incluindo Sequence SEQ_BORQVL'

CREATE OR REPLACE FUNCTION func_createSequence() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_statio_all_sequences WHERE relname = 'seq_borqvl' and schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE SEQUENCE <SCHEMA>.SEQ_BORQVL START WITH 100000;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createSequence();
 DROP FUNCTION func_createSequence();





CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.BACK_OFFICE_REQUEST_STATUS WHERE COD_BACK_OFFICE_STATUS = 0;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.BACK_OFFICE_REQUEST_STATUS SET COD_BACK_OFFICE_STATUS = 0, NM_STATUS = 'PENDENTE', VERSION = 0 WHERE COD_BACK_OFFICE_STATUS = 0;
  ELSE
    INSERT INTO <SCHEMA>.BACK_OFFICE_REQUEST_STATUS (COD_BACK_OFFICE_STATUS,  NM_STATUS, VERSION)
                                             VALUES (                     0, 'PENDENTE',       0);
  END IF;
  RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();


CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.BACK_OFFICE_REQUEST_STATUS WHERE COD_BACK_OFFICE_STATUS = 1;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.BACK_OFFICE_REQUEST_STATUS SET COD_BACK_OFFICE_STATUS = 1, NM_STATUS = 'ENVIADO', VERSION = 0 WHERE COD_BACK_OFFICE_STATUS = 1;
  ELSE
    INSERT INTO <SCHEMA>.BACK_OFFICE_REQUEST_STATUS (COD_BACK_OFFICE_STATUS, NM_STATUS, VERSION)
                                             VALUES (                     1, 'ENVIADO',       0);
  END IF;
  RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();


CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.BACK_OFFICE_REQUEST_STATUS WHERE COD_BACK_OFFICE_STATUS = 2;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.BACK_OFFICE_REQUEST_STATUS SET COD_BACK_OFFICE_STATUS = 2, NM_STATUS = 'REJEITADO', VERSION = 0 WHERE COD_BACK_OFFICE_STATUS = 2;
  ELSE
    INSERT INTO <SCHEMA>.BACK_OFFICE_REQUEST_STATUS (COD_BACK_OFFICE_STATUS,   NM_STATUS, VERSION)
                                             VALUES (                     2, 'REJEITADO',       0);
  END IF;
  RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();


